const express = require('express')
const Result = require('../models/Result')
const {
  orLike,
  orLimit,
  queryTotal,
  querySqlTotal,
  querySql,
  insert,
  deleteData,
  update,
  queryOne
} = require('../db/index')

const router = express.Router()

/**
 * 修改歌曲 是否喜欢的状态
 */
router.post('/setSongLike', async(req, res) => {
  let sheetId = req.body.sheetId
  let songId = req.body.songId
  let isLike = req.body.isLike
  // req.body.sheetId req.body.songId
  await update(`UPDATE sheet_song SET isLike = ${isLike} WHERE sheetId = ${sheetId} and songId = ${songId}`)
  new Result(true, '修改成功').success(res)
})

/**
 * 获取歌曲的详细信息
 */
router.get('/getSongDetail/:songId', async(req, res) => {
  let result = await queryOne(`SELECT t1.*, t2.singerName, t3.lyric from song t1 left join singer t2 on t1.singer = t2.id left join lyric t3 on t1.id = t3.songId where t1.id = ${req.params.songId}`)
  new Result(result, '查询成功').success(res)
})

/**
 * 获取该歌曲在那些歌单中
 */
router.post('/getSongBeToSheet', async(req, res) => {
  let result = await querySql(`
    SELECT
      id
    FROM
      sheet
    WHERE
      userId = ${req.body.userId}
      AND id IN ( SELECT sheetId FROM sheet_song WHERE songId = ${req.body.songId} )
  `)
  new Result(result.map(item => item.id), '查询成功').success(res)
})

/**
 * 增加播放历史
 */
router.post('/addPlayHistory', async(req, res) => {
  let result = await insert(`insert into play_history(userId, songId) values(${req.body.userId}, ${req.body.songId})`)

  new Result(true, '新增成功').success(res)
})

/**
 * 新增歌曲
 */
router.post('/addSong', async(req, res) => {
  const {name, src, singer, album, duration} = req.body
  let result = await insert(`insert into song(id, name, src, singer, album, duration) values(null, ${name}, ${src}, ${singer}, ${album}, ${duration})`)

  new Result(result, '新增成功').success(res)
})

// 删除歌曲
router.post('/deleteSong', async (req, res, next) => {
  await deleteData(`delete from song where id = ${req.body.id}`)
  new Result(true, '删除成功').success(res)
})

// 修改歌曲
router.post('/updateSong', async (req, res, next) => {
  let temp = ''
  // sql拼接
  for(let key in req.body) {
    if(req.body[key] && !['id', 'isEdit', 'create_time'].includes(key)) {
      temp += `${key} = '${req.body[key]}',`
    }
  }
  temp = temp.substr(0, temp.length - 1)
  // 修改
  update(`UPDATE song SET ${temp} WHERE id = ${req.body.id}`)
  new Result(true, '保存成功').success(res)
})

// 获取歌曲分页列表
router.post('/getSongPage', async (req, res, next) => {
  let result = await querySql(
    `select * from song ${orLike(req, 'name')} ${orLimit(req)}`
    )
  let total = await querySqlTotal(`select * from song ${orLike(req, 'name')}`)
  new Result({data: result, total}, '查询成功').success(res)
})

module.exports = router